﻿///Author: Alexander Persson
///License: MS-PL

using ap.DatabaseUtil.Models;
using System;
using System.Collections.Generic;
using System.Data.Objects;
using System.Text;
using System.Linq;

namespace ap.DatabaseUtil.Helpers
{
    internal class DbHelper
    {
        /// <summary>
        /// Reads the lengths from database
        /// </summary>
        /// <param name="context">The ObjectContext we want to use to query the database</param>
        /// <param name="databaseName">The name of the database</param>
        /// <param name="databaseSchema">The name of the database schema</param>
        /// <returns>A list of maximum lenghts</returns>
        public static IEnumerable<Limit> GetLimits(ObjectContext context, string databaseName, string databaseSchema)
        {
            var query = new StringBuilder(@"Select TABLE_NAME as TableName, COLUMN_NAME as ColumnName, CHARACTER_MAXIMUM_LENGTH as Length, TABLE_SCHEMA as SchemaName
                                          FROM ");
            query.Append(databaseName);
            query.Append(@".INFORMATION_SCHEMA.COLUMNS
                                          WHERE TABLE_SCHEMA='");
            query.Append(databaseSchema);
            query.Append(@"'
                                          AND DATA_TYPE IN ('varchar','nvarchar','char','nchar')
	                                      AND CHARACTER_MAXIMUM_LENGTH > 0");

            try
            {
                var result = context.ExecuteStoreQuery<Limit>(query.ToString());
                return result.ToList();
            }
            catch (Exception ex)
            {
                return Enumerable.Empty<Limit>();
            }
        }
    }
}
